package com.cheung.shopkeeper.common.excel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;


/**
 * @description:
 *
 * @author ghdong
 * 
 */
public class ExcelCellUtil {

	public static String appendString(List<String> inputList, String rexg){
		StringBuffer buf = new StringBuffer();
		for(String str : inputList){
			buf.append(str).append(rexg);
		}
		String str = buf.toString();
		if(!"".equals(str)){
			str = str.substring(0,str.lastIndexOf(rexg)-1);
		}
		
		return str;
	}
	public static boolean isEmpty(String str) {
		return str == null || str.trim().length() == 0;
	}
	/**
	 * 获取当前CELL内容
	 */
	public static String getCellText(HSSFCell cell) {
		if (cell == null)
			return "";
		int type = cell.getCellType();
		switch (type) {
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
		case HSSFCell.CELL_TYPE_NUMERIC:
			double value = cell.getNumericCellValue();
			if(value==(int)value){
				return (new Integer((int)value)).toString();
			}else{
				return Double.toString(value);
			}
			
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getRichStringCellValue().getString().trim();
		}
		return "";
	}
	
	
	/**
	 * 获取当前CELL内容
	 * param:该列的标题 title
	 * param：单元格 cell
	 */
	public static String getCellTextWithTitle(HSSFCell cell,String title) {
		if (cell == null)
			return "";
		
		int type = cell.getCellType();
		
		switch (type) {
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
			
		case HSSFCell.CELL_TYPE_NUMERIC:			
			//对于手机号会变成指数形式，时间会变成Double情况的处理
			if (HSSFDateUtil.isCellDateFormatted(cell)) { 
				//单元格为时间类型
                double d = cell.getNumericCellValue();  
                Date date = HSSFDateUtil.getJavaDate(d); 
                SimpleDateFormat dformat=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                return dformat.format(date);            
             }else{
            	 //非时间类型
                 if(title != null && 
                		 (title.indexOf("编号") >= 0 || title.indexOf("编码") >= 0
                		 || title.indexOf("手机") >= 0 || title.indexOf("电话") >= 0
                		 || title.indexOf("ID") >= 0)){
                	 DecimalFormat df = new DecimalFormat("0");
                	 return df.format(cell.getNumericCellValue());
                 }else{
                	 //下面是普通数值类型,如货币
                 	double value = cell.getNumericCellValue();						
          			if(value==(int)value){
          				return (new Integer((int)value)).toString();
          			}else{
          				return Double.toString(value);
          			}
                 }            	
             }           		
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getRichStringCellValue().getString().trim();
			
		case HSSFCell.CELL_TYPE_FORMULA: 
			return cell.getCellFormula() + "";
			
		case HSSFCell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue() + "";
			
		case HSSFCell.CELL_TYPE_ERROR:
			return "非法字符";
		}			
		return "";
	}
	
	public static boolean isRowBlank(HSSFRow rowLine){
		boolean blankRow = true;		
		int coloumNum =	rowLine.getPhysicalNumberOfCells();
		//行数是从1开始数的，需要测试一下		
		for(int i=0; i < coloumNum; i++){
			HSSFCell cell = rowLine.getCell(i);
			String cellStr = getCellText(cell);
			if(cellStr != null && !cellStr.equals("")){
				blankRow = false;
				break;
			}
		}		
		return blankRow;		
	}
	
	/**
	 * 不区分大小写判断2个字符串是否相同(null与空字符串""比较为相等)
	 * @param firstStr
	 * @param secondStr
	 * @return 
	 */
	public static boolean isEqualIgnoreCase(String firstStr,String secondStr) {
		if(firstStr == null){
			firstStr = "";
		}
		if(secondStr == null){
			secondStr = "";
		}
		if(firstStr.equalsIgnoreCase(secondStr)){
			return true;
		}
		return false;
	}
	/**
	 * 区分大小写判断2个字符串是否相同(null与空字符串""比较为相等)
	 * @param firstStr
	 * @param secondStr
	 * @return 
	 */
	public static boolean isEqual(String firstStr,String secondStr) {
		if(firstStr == null){
			firstStr = "";
		}
		if(secondStr == null){
			secondStr = "";
		}
		if(firstStr.equalsIgnoreCase(secondStr)){
			return true;
		}
		return false;
	}
	
	/**
     * 检查导入excel的标题和数据是否为空 
     */
    public static boolean isEmptyTitleAndData(HSSFSheet sheet){
    	boolean isEmpty = false;
        int titleIndex = 0;//第一行
        HSSFRow titleRow = sheet.getRow(titleIndex);

        if(titleRow == null){
        	isEmpty  = true;
        }

        int index = 1;//除去标题行的数据行
        HSSFRow row = sheet.getRow(index);
        if(row==null){
            isEmpty = true;
        }
        return isEmpty;
    }
    
    public static boolean isMatchTemplate(HSSFSheet sheet, String title){
    	boolean isMatch = false;    	
    	if(title == null){
    		isMatch = false;
    	}    	
    	int coloum = sheet.getRow(0).getPhysicalNumberOfCells();    	
    	if(coloum > 0 && title != null){			
			StringBuilder excelTile = new StringBuilder();			
			HSSFRow titleRow = sheet.getRow(0);
			for (int i = 0; i < coloum; i++) {
				excelTile.append(getCellText(titleRow.getCell(i))+",");
			}
			String excelTitleStr = excelTile.toString().substring(0, excelTile.toString().length()-1);
			if(title.equals(excelTitleStr)){
				isMatch = true;
			}			
    	}
        return isMatch;
    }
    
    
    
}

